<?php
class Helper_BSS_XLS
{
	static function exportXls($data, $output_filename = 'export.xls')
	{
		$data = iconv("UTF-8", "UTF-16LE", $data);
		$data = "\xFF\xFE" . $data;
		header( "Content-Type: application/vnd.ms-excel; charset=u");
		header( "Content-Type: application/octet-stream" );
		header( "Content-Type: application/download" );
		header( 'Content-Disposition:inline; filename="' . $output_filename . '"' );
		header( "Content-Transfer-Encoding: binary" );
		header( "Cache-Control: must-revalidate, post-check=0, pre-check=0");
		header( "Pragma: no-cache");
		echo $data;
		exit;
	}
	
	static function xlsFileToArray($filename, $file_id)
	{
		require_once(Q::ini('custom_system/lib_dir') . 'PHPExcel.php');
		$reader = PHPExcel_IOFactory::createReader('Excel5');
		$phpexcel = $reader->load($filename);
		$worksheet = $phpexcel->getActiveSheet();
		$highest_row = $worksheet->getHighestRow();
		$highest_column = PHPExcel_Cell::columnIndexFromString($worksheet->getHighestColumn());
		$key = array();
		for($j = 0; $j < $highest_column; $j++)
		{
			if (trim((string)$worksheet->getCellByColumnAndRow($j, 1)->getValue()))
			{
				$key[] = (string)$worksheet->getCellByColumnAndRow($j, 1)->getValue();
			}
		}
		$data = array();
		for ($i = 2; $i <= $highest_row; $i++)
		{
			$row = array();
			for ($j = 0; $j < count($key); $j++)
			{
				$row[$key[$j]] = (string)$worksheet->getCellByColumnAndRow($j, $i)->getValue();
			}
			$row['file_id'] = $file_id;
			array_push($data, $row);
		}
		return $data;
	}
	
	static function ebayProductInfoExport($product_info)
	{
		include Q::ini('custom_system/lib_dir') . 'PHPExcel.php';
		$phpexcel = new PHPExcel();
		$phpexcel->getProperties()->setCreator(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
		$phpexcel->getProperties()->setLastModifiedBy(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
		$phpexcel->getProperties()->setTitle('ebay产品描述模板表');
		$phpexcel->getProperties()->setSubject('ebay产品描述模板表');
		$phpexcel->getProperties()->setDescription('ebay产品描述模板表-运营系统支撑系统');
		$phpexcel->getProperties()->setKeywords('ebay产品描述模板表');
		$phpexcel->getProperties()->setCategory('ebay产品描述模板表');
		$phpexcel->setActiveSheetIndex(0);
		$phpexcel->getActiveSheet()->setTitle('ebay产品描述模板表');
		$phpexcel->getActiveSheet()->setCellValue('A1', 'language');
		$phpexcel->getActiveSheet()->setCellValue('B1', 'SKU');
		$phpexcel->getActiveSheet()->setCellValue('C1', 'Item_Name');
		$phpexcel->getActiveSheet()->setCellValue('D1', 'Item_Title');
		$phpexcel->getActiveSheet()->setCellValue('E1', 'Description');
		$i = 2;
		foreach ($product_info as $pi)
		{
			$phpexcel->getActiveSheet()->setCellValue('A' . $i, Helper_BSS_Normal::getIniOne('online_ebay_product_language', 'value', $pi['language'], 'text'));
			$phpexcel->getActiveSheet()->setCellValue('B' . $i, $pi['number']);
			$phpexcel->getActiveSheet()->setCellValue('C' . $i, $pi['name']);
			$phpexcel->getActiveSheet()->setCellValue('D' . $i, $pi['title']);
			$phpexcel->getActiveSheet()->setCellValue('E' . $i, $pi['description']);
			++$i;
		}
		$outputFileName = "ebay产品描述模板.xls";
		$objWriter = new PHPExcel_Writer_Excel5($phpexcel);
		header("Content-Type: application/vnd.ms-excel");
		header("Content-type: application/octet-stream");
		header("Content-type: application/download");
		header('Content-Disposition:inline;filename="' . $outputFileName . '"');
		header("Content-Transfer-Encoding:binary");
		header("Cache-Control: must-revalidate, post-check=0, pri-check=0");
		header("Pragma: no-cache");
		$objWriter->Save('php://output');
		exit;
	}
	
	static function onlineEbayListingWrongSaveToXls($wrong_listings, $file_name)
	{
		if (! is_file(Q::ini('custom_system/wrong_listing_dir') . $file_name))
		{
			include Q::ini('custom_system/lib_dir') . 'PHPExcel.php';
			$phpexcel_title = new PHPExcel();
			$phpexcel_title->getProperties()->setCreator(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
			$phpexcel_title->getProperties()->setLastModifiedBy(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
			$phpexcel_title->getProperties()->setTitle('ebay上架产品出错表');
			$phpexcel_title->getProperties()->setSubject('ebay产品描述模板表');
			$phpexcel_title->getProperties()->setDescription('ebay上架产品出错表-运营系统支撑系统');
			$phpexcel_title->getProperties()->setKeywords('ebay上架产品出错表');
			$phpexcel_title->getProperties()->setCategory('ebay上架产品出错表');
			$phpexcel_title->setActiveSheetIndex(0);
			$phpexcel_title->getActiveSheet()->setTitle('ebay上架产品出错表');
			$phpexcel_title->getActiveSheet()->setCellValue('A1', 'shop_name');
			$phpexcel_title->getActiveSheet()->setCellValue('B1', 'SKU');
			$phpexcel_title->getActiveSheet()->setCellValue('C1', 'shop_item_id');
			$phpexcel_title->getActiveSheet()->setCellValue('D1', 'site_id');
			$phpexcel_title->getActiveSheet()->setCellValue('E1', 'item_url');
			$objWriter = new PHPExcel_Writer_Excel5($phpexcel_title);
			$objWriter->Save(Q::ini('custom_system/wrong_listing_dir') . $file_name);
		}
		require_once(Q::ini('custom_system/lib_dir') . 'PHPExcel.php');
		$reader = PHPExcel_IOFactory::createReader('Excel5');
		$phpexcel = $reader->load(Q::ini('custom_system/wrong_listing_dir') . $file_name);
		$row = $phpexcel->getActiveSheet()->getHighestRow();
		$row++;
		foreach ($wrong_listings as $wl)
		{
			$shop = Shop::find('id=?', $wl['shop_id'])->asArray()->getOne();
			$phpexcel->getActiveSheet()->setCellValue('A' . $row, $shop['name']);
			$phpexcel->getActiveSheet()->getCell('B' . $row)->setValueExplicit($wl['sku'], PHPExcel_Cell_DataType::TYPE_STRING);
			$phpexcel->getActiveSheet()->getCell('C' . $row)->setValueExplicit($wl['shop_item_id'], PHPExcel_Cell_DataType::TYPE_STRING);
			$phpexcel->getActiveSheet()->setCellValue('D' . $row, $wl['site_id']);
			$phpexcel->getActiveSheet()->setCellValue('E' . $row, $wl['item_url']);
			$row++;
		}
		$objWriter = new PHPExcel_Writer_Excel5($phpexcel);
		$objWriter->Save(Q::ini('custom_system/wrong_listing_dir') . $file_name);
	}
	
	static function productBasicExport($data)
	{
		include Q::ini('custom_system/lib_dir') . 'PHPExcel.php';
		$phpexcel = new PHPExcel();
		$phpexcel->getProperties()->setCreator(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
		$phpexcel->getProperties()->setLastModifiedBy(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
		$phpexcel->getProperties()->setTitle('产品基本信息表');
		$phpexcel->getProperties()->setSubject('产品基本信息表');
		$phpexcel->getProperties()->setDescription('产品基本信息表-运营系统支撑系统');
		$phpexcel->getProperties()->setKeywords('产品基本信息表');
		$phpexcel->getProperties()->setCategory('产品基本信息表');
		$phpexcel->setActiveSheetIndex(0);
		$phpexcel->getActiveSheet()->setTitle('产品基本信息表');
		$phpexcel->getActiveSheet()->setCellValue('A1', 'number');
		$phpexcel->getActiveSheet()->setCellValue('B1', 'name');
		$phpexcel->getActiveSheet()->setCellValue('C1', 'weight');
		$phpexcel->getActiveSheet()->setCellValue('D1', 'purchase_price');
		$phpexcel->getActiveSheet()->setCellValue('E1', 'preferred_provider_id');
		$phpexcel->getActiveSheet()->setCellValue('F1', 'product_categories_id');
		$phpexcel->getActiveSheet()->setCellValue('G1', 'declared_value');
		$phpexcel->getActiveSheet()->setCellValue('H1', 'declared_name_cn');
		$phpexcel->getActiveSheet()->setCellValue('I1', 'declared_name_en');
		$phpexcel->getActiveSheet()->setCellValue('J1', 'notes');
		$phpexcel->getActiveSheet()->setCellValue('K1', 'img');
		$i = 2;
		foreach ($data as $d)
		{
			$phpexcel->getActiveSheet()->getCell('A' . $i)->setValueExplicit($d['number'], PHPExcel_Cell_DataType::TYPE_STRING);
			$phpexcel->getActiveSheet()->setCellValue('B' . $i, $d['name']);
			$phpexcel->getActiveSheet()->setCellValue('C' . $i, $d['weight']);
			$phpexcel->getActiveSheet()->setCellValue('D' . $i, $d['purchase_price']);
			$phpexcel->getActiveSheet()->setCellValue('E' . $i, $d['preferred_provider_id']);
			$phpexcel->getActiveSheet()->setCellValue('F' . $i, $d['product_categories_id']);
			$phpexcel->getActiveSheet()->setCellValue('G' . $i, $d['declared_value']);
			$phpexcel->getActiveSheet()->setCellValue('H' . $i, $d['declared_name_cn']);
			$phpexcel->getActiveSheet()->setCellValue('I' . $i, $d['declared_name_en']);
			$phpexcel->getActiveSheet()->setCellValue('J' . $i, $d['notes']);
			$phpexcel->getActiveSheet()->setCellValue('K' . $i, $d['img']);
			$i++;
		}
		$objWriter = new PHPExcel_Writer_Excel5($phpexcel);
		$outputFileName = "产品基本信息表-" . date('YmdHis', CURRENT_TIMESTAMP) . '.xls';
		header("Content-Type: application/vnd.ms-excel");
		header("Content-type: application/octet-stream");
		header("Content-type: application/download");
		header('Content-Disposition:inline;filename="' . $outputFileName . '"');
		header("Content-Transfer-Encoding:binary");
		header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
		header("Pragma: no-cache");
		$objWriter->save('php://output');
		exit;
	}

	static function productProviderExport($data)
	{
		include Q::ini('custom_system/lib_dir') . 'PHPExcel.php';
		$phpexcel = new PHPExcel();
		$phpexcel->getProperties()->setCreator(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
		$phpexcel->getProperties()->setLastModifiedBy(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
		$phpexcel->getProperties()->setTitle('供应商信息表');
		$phpexcel->getProperties()->setSubject('供应商信息表');
		$phpexcel->getProperties()->setDescription('供应商信息表-运营系统支撑系统');
		$phpexcel->getProperties()->setKeywords('供应商信息表');
		$phpexcel->getProperties()->setCategory('供应商信息表');
		$phpexcel->setActiveSheetIndex(0);
		$phpexcel->getActiveSheet()->setTitle('供应商信息表');
		$phpexcel->getActiveSheet()->setCellValue('A1', '编号');
		$phpexcel->getActiveSheet()->setCellValue('B1', '供应商名称');
		$phpexcel->getActiveSheet()->setCellValue('C1', '联系人');
		$phpexcel->getActiveSheet()->setCellValue('D1', '产品');
		$phpexcel->getActiveSheet()->setCellValue('E1', '联系电话');
		$phpexcel->getActiveSheet()->setCellValue('F1', 'QQ');
		$phpexcel->getActiveSheet()->setCellValue('G1', '地址');
		$phpexcel->getActiveSheet()->setCellValue('H1', '采购员');
		$i = 2;
		foreach ($data as $d)
		{
			$phpexcel->getActiveSheet()->getCell('A' . $i)->setValueExplicit($d['id'], PHPExcel_Cell_DataType::TYPE_STRING);
			$phpexcel->getActiveSheet()->setCellValue('B' . $i, $d['name']);
			$phpexcel->getActiveSheet()->setCellValue('C' . $i, $d['contact_person']);
			$phpexcel->getActiveSheet()->setCellValue('D' . $i, $d['categories'][0]['name']);
			$phpexcel->getActiveSheet()->setCellValue('E' . $i, $d['phone']);
			$phpexcel->getActiveSheet()->setCellValue('F' . $i, $d['qq']);
			$phpexcel->getActiveSheet()->setCellValue('G' . $i, $d['address']);
			$phpexcel->getActiveSheet()->setCellValue('H' . $i, $d['follow_user_name']);
			$i++;
		}
		$objWriter = new PHPExcel_Writer_Excel5($phpexcel);
		$outputFileName = "供应商信息表-" . date('YmdHis', CURRENT_TIMESTAMP) . '.xls';
		header("Content-Type: application/vnd.ms-excel");
		header("Content-type: application/octet-stream");
		header("Content-type: application/download");
		header('Content-Disposition:inline;filename="' . $outputFileName . '"');
		header("Content-Transfer-Encoding:binary");
		header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
		header("Pragma: no-cache");
		$objWriter->save('php://output');
		exit;
	}
	static function importNewEggOrderByXls($filename)
	{
		require_once(Q::ini('custom_system/lib_dir') . 'PHPExcel.php');
		$phpexcel = new PHPExcel();
		$phpreader = new PHPExcel_Reader_Excel5();
		$phpexcel = $phpreader->load($filename);
		$sheet = $phpexcel->getActiveSheet();
		$height = $sheet->getHighestRow();
		$width = PHPExcel_Cell::columnIndexFromString($sheet->getHighestColumn());
		$data = array();
		for($i = 2; $i <= $height; $i++)
		{
			$shop_order_number = $sheet->getCellByColumnAndRow(2, $i)->getValue();
			if( ! empty($shop_order_number))
			{
				$row = array();
				for($j = 0; $j < $width; $j++)
				{
					$cell = $sheet->getCellByColumnAndRow($j, $i)->getValue();
					array_push($row, $cell);
				}
				array_push($data, $row);
			}
		}
		return $data;
	}
	
	static function shippingFeeExport($data)
	{
		include Q::ini('custom_system/lib_dir') . 'PHPExcel.php';
		$phpexcel = new PHPExcel();
		$phpexcel->getProperties()->setCreator(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
		$phpexcel->getProperties()->setLastModifiedBy(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
		$phpexcel->getProperties()->setTitle('订单运费信息表');
		$phpexcel->getProperties()->setSubject('订单运费信息表');
		$phpexcel->getProperties()->setDescription('订单运费信息表-运营系统支撑系统');
		$phpexcel->getProperties()->setKeywords('订单运费信息表');
		$phpexcel->getProperties()->setCategory('订单运费信息表');
		$phpexcel->setActiveSheetIndex(0);
		$phpexcel->getActiveSheet()->setTitle('订单运费信息表');
		$phpexcel->getActiveSheet()->setCellValue('A1', '本地单号');
		$phpexcel->getActiveSheet()->setCellValue('B1', '网站');
		$phpexcel->getActiveSheet()->setCellValue('C1', '发货时间');
		$phpexcel->getActiveSheet()->setCellValue('D1', '物流订单号');
		$phpexcel->getActiveSheet()->setCellValue('E1', '物流包裹号');
		$phpexcel->getActiveSheet()->setCellValue('F1', '邮寄方式');
		$phpexcel->getActiveSheet()->setCellValue('G1', '重量');
		$phpexcel->getActiveSheet()->setCellValue('H1', '币种');
		$phpexcel->getActiveSheet()->setCellValue('I1', '金额');
		$i = 2;
		foreach ($data as $d)
		{
			$phpexcel->getActiveSheet()->getCell('A' . $i)->setValueExplicit($d['number'], PHPExcel_Cell_DataType::TYPE_STRING);
			$phpexcel->getActiveSheet()->setCellValue('B' . $i, $d['shop_name']);
			$phpexcel->getActiveSheet()->setCellValue('C' . $i, $d['shipped_time']);
			$phpexcel->getActiveSheet()->setCellValueExplicit('D' . $i, $d['lsp_number'], PHPExcel_Cell_DataType::TYPE_STRING);
			$phpexcel->getActiveSheet()->setCellValueExplicit('E' . $i, $d['tracking_number'], PHPExcel_Cell_DataType::TYPE_STRING);
			$phpexcel->getActiveSheet()->setCellValue('F' . $i, $d['shipping_method_name']);
			$phpexcel->getActiveSheet()->setCellValue('G' . $i, sprintf('%.2f', $d['weight']) . 'g');
			$phpexcel->getActiveSheet()->setCellValue('H' . $i, $d['postage_currency_code']);
			$phpexcel->getActiveSheet()->setCellValue('I' . $i, sprintf('%.2f', $d['postage']));
			$i++;
		}
		$objWriter = new PHPExcel_Writer_Excel5($phpexcel);
		$outputFileName = "订单运费信息表-" . date('YmdHis', CURRENT_TIMESTAMP) . '.xls';
		header("Content-Type: application/vnd.ms-excel");
		header("Content-type: application/octet-stream");
		header("Content-type: application/download");
		header('Content-Disposition:inline;filename="' . $outputFileName . '"');
		header("Content-Transfer-Encoding:binary");
		header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
		header("Pragma: no-cache");
		$objWriter->save('php://output');
		exit;
	}
	
	static function ProductTraceListExport($data)
	{
		include Q::ini('custom_system/lib_dir') . 'PHPExcel.php';
		$phpexcel = new PHPExcel();
		$phpexcel->getProperties()->setCreator(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
		$phpexcel->getProperties()->setLastModifiedBy(Q::ini('custom_system/system_name') . Q::ini('custom_system/version'));
		$phpexcel->getProperties()->setTitle('产品上架跟踪查询表');
		$phpexcel->getProperties()->setSubject('产品上架跟踪查询表');
		$phpexcel->getProperties()->setDescription('产品上架跟踪查询表-运营系统支撑系统');
		$phpexcel->getProperties()->setKeywords('产品上架跟踪查询表');
		$phpexcel->getProperties()->setCategory('产品上架跟踪查询表');
		$phpexcel->setActiveSheetIndex(0);
		$phpexcel->getActiveSheet()->setTitle('产品上架跟踪查询表');
		$phpexcel->getActiveSheet()->setCellValue('A1', 'SKU');
		$phpexcel->getActiveSheet()->setCellValue('B1', '产品名称');
		$phpexcel->getActiveSheet()->setCellValue('C1', '周销');
		$phpexcel->getActiveSheet()->setCellValue('D1', '月销');
		$phpexcel->getActiveSheet()->setCellValue('E1', '销售状态');
		$phpexcel->getActiveSheet()->setCellValue('F1', '采购状态');
		$phpexcel->getActiveSheet()->setCellValue('G1', '店铺数量');
		$i = 2;
		foreach ($data as $d)
		{
			$phpexcel->getActiveSheet()->getCell('A' . $i)->setValueExplicit($d['number'], PHPExcel_Cell_DataType::TYPE_STRING);
			$phpexcel->getActiveSheet()->setCellValue('B' . $i, $d['product_name']);
			$phpexcel->getActiveSheet()->setCellValue('C' . $i, $d['weekly_sales']);
			$phpexcel->getActiveSheet()->setCellValue('D' . $i, $d['monthly_sales']);
			$phpexcel->getActiveSheet()->setCellValue('E' . $i, Helper_BSS_Normal::getIniOne('product_sales_status', 'value', $d['sales_status'], 'name'));
			$phpexcel->getActiveSheet()->setCellValue('F' . $i, Helper_BSS_Normal::getIniOne('product_purchase_status', 'value', $d['purchase_status'], 'name'));
			$phpexcel->getActiveSheet()->setCellValue('G' . $i, $d['shop_count']);
			$i++;
		}
		$objWriter = new PHPExcel_Writer_Excel5($phpexcel);
		$outputFileName = "产品上架跟踪查询表-" . date('YmdHis', CURRENT_TIMESTAMP) . '.xls';
		header("Content-Type: application/vnd.ms-excel");
		header("Content-type: application/octet-stream");
		header("Content-type: application/download");
		header('Content-Disposition:inline;filename="' . $outputFileName . '"');
		header("Content-Transfer-Encoding:binary");
		header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
		header("Pragma: no-cache");
		$objWriter->save('php://output');
		exit;
	}
}